/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.mycompany.ingsoc2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author sfreiman
 */
public class PeliculaDAOMYSQL implements PeliculaDAO {

    Connection conn;

    /**
     *
     * @param myconn
     */
    public PeliculaDAOMYSQL(Connection myconn) {
        conn = myconn;
    }

    public boolean crear(Pelicula pp) throws SQLException {
        String query;
        int returnvalue;
        query = "INSERT INTO Pelicula (titulo,descripcion,director,actor_principal,duracion,id_genero) "
                + "VALUES(?,?,?,?,?,?)";

        PreparedStatement pstmt = conn.prepareStatement(query);
        pstmt.setString(1, pp.getTitulo());
        pstmt.setString(2, pp.getDescripcion());
        pstmt.setString(3, pp.getDirector());
        pstmt.setString(4, pp.getActorPrincipal());
        pstmt.setInt(5, pp.getDuracion());
        pstmt.setInt(6, pp.getGenero().getId());
        returnvalue = pstmt.executeUpdate();

        if (returnvalue == 0) {
            throw new SQLException("Error de creacion de pelicula.");

        }

        PreparedStatement psIdentity = conn.prepareStatement("CALL IDENTITY()");

        ResultSet resultSet = psIdentity.executeQuery();
        if (resultSet.next()) {
            int lastid = resultSet.getInt(1);
            //System.out.println("Last id: " + lastid);
            pp.setId(lastid);
            resultSet.close();
        }

        return (returnvalue > 0);

    }

    public boolean borrar(Pelicula pp) throws SQLException {

        String query;
        PreparedStatement pstst;
        query = "DELETE from Pelicula WHERE id =?";

        pstst = conn.prepareStatement(query);
        pstst.setInt(1, pp.getId());
        return pstst.executeUpdate() == 1;
    }

    public boolean guardar(Pelicula pp) throws SQLException {
        String query;
        query = "UPDATE Pelicula SET titulo=?,descripcion=?,director=?,actor_principal=?,duracion=?,id_genero=? WHERE id=?";
        PreparedStatement pstmt = conn.prepareStatement(query);
        pstmt.setString(1, pp.getTitulo());
        pstmt.setString(2, pp.getDescripcion());
        pstmt.setString(3, pp.getDirector());
        pstmt.setString(4, pp.getActorPrincipal());
        pstmt.setInt(5, pp.getDuracion());
        pstmt.setInt(6, pp.getGenero().getId());
        pstmt.setInt(7, pp.getId());
        return pstmt.executeUpdate() == 1;
    }

    public List<Pelicula> listar() throws SQLException {
        String query;
        Statement stst;
        ResultSet rst1;
        List listaDePeliculas = new ArrayList<Pelicula>();

        Pelicula nPelicula;

        query = "SELECT * FROM Pelicula";
        stst = conn.createStatement();
        rst1 = stst.executeQuery(query);
        while (rst1.next()) {
            nPelicula = new Pelicula();
            nPelicula.setTitulo(rst1.getString("titulo"));
            nPelicula.setDescripcion(rst1.getString("descripcion"));
            nPelicula.setDirector(rst1.getString("director"));
            nPelicula.setActorPrincipal(rst1.getString("actor_principal"));
            nPelicula.setDuracion(rst1.getInt("duracion"));

            nPelicula.setGenero(new Genero(rst1.getInt("id_genero")));
            nPelicula.setId(rst1.getInt("id"));

            listaDePeliculas.add(nPelicula);
        }

        return listaDePeliculas;
    }

    /**
     *
     * @param id
     * @param titulo
     * @param director
     * @return
     * @throws SQLException
     */
    public List<Pelicula> listar(int id, String titulo, String director) throws SQLException {
        String query;
        PreparedStatement pstmt;
        ResultSet rst1;
        List listaDePeliculas = new ArrayList<Pelicula>();

        Pelicula nPelicula;
        query = "SELECT * FROM Pelicula ";
        if (id >= 0) {
            query = query + " WHERE id = ? and titulo like ? and director like ?";
            pstmt = conn.prepareStatement(query);
            pstmt.setInt(1, id);
            pstmt.setString(2, "%" + titulo + "%");
            pstmt.setString(3, "%" + director + "%");
        } else {
            query = query + " WHERE titulo LIKE ? and director LIKE ?";
            pstmt = conn.prepareStatement(query);

            pstmt.setString(1, "%" + titulo + "%");
            pstmt.setString(2, "%" + director + "%");

        }

        rst1 = pstmt.executeQuery();

        while (rst1.next()) {
            nPelicula = new Pelicula();
            nPelicula.setTitulo(rst1.getString("titulo"));
            nPelicula.setDescripcion(rst1.getString("descripcion"));
            nPelicula.setDirector(rst1.getString("director"));
            nPelicula.setActorPrincipal(rst1.getString("actor_principal"));
            nPelicula.setDuracion(rst1.getInt("duracion"));

            nPelicula.setGenero(new Genero(rst1.getInt("id_genero")));
            nPelicula.setId(rst1.getInt("id"));

            listaDePeliculas.add(nPelicula);
        }

        return listaDePeliculas;
    }

    public List<Pelicula> listar(int id) throws SQLException {
        return this.listar(id, "", "");
    }

    public List<Pelicula> listar(String titulo, String director) throws SQLException {
        return this.listar(-1, titulo, director);
    }

    @Override
    public List<Pelicula> listar(Genero mGenero) throws SQLException {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public List<Pelicula> listar(String titulo, String director, Genero mGenero) throws SQLException {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

}
